﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client;
using System.Web;
using System.Web.Caching;


using LMD.Constant;
using LMD.Lib.Oracle;

namespace Cached
{
    /// <summary>
    /// Summary description for CompanyReport_SaldoAkhirTable
    /// </summary>
    public class CompanyReport_SaldoAkhirTable : CacheAbstraction
    {
        public DataTable GetSaldoAkhirRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetSaldoAkhirRekap(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetSaldoAkhirRekap(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_SALDO_AKHIR_REKAP, 
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if (bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxSelect =
                   "SUM (a.amount_aft_disc_idr_total) as \"JUMLAH_PENDAPATAN\", "+
                   "dper.MONTH_NUM, "+
                   "dper.year_num";
                String sxQuery = String.Format(
                    @"
                      SELECT   {0}
                        FROM   dim_period dper,
                               t_fact_saldo_akhir_target a,
                               dim_pelanggan dpel,
                               dim_organisasi_sales dos,
                               dim_network_owner dno
                       WHERE       a.network_owner_key = dno.network_owner_key
                               AND a.pelanggan_key = dpel.pelanggan_key
                               AND a.period_bulan_data_key = dper.period_key
                               AND a.organisasi_sales_key = dos.organisasi_sales_key
                               --AND dper.year_num = 2011
                               --AND dper.MONTH_NUM = 5
                               AND to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND dpel.COMPANY_ID = :prmID --103893
                    GROUP BY   dper.year_num,
                               --dper.month_name,
                               dper.MONTH_NUM
                               --dpel.company_no,
                               --dpel.company_name,
                               --dper.DAY_NUM
                    order by   dper.year_num,
                               dper.MONTH_NUM 
                    ",sxSelect);

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }

        public DataTable GetSaldoAkhirDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd)
        {
            return GetSaldoAkhirDetail(iCompanyId, dtmStart, dtmEnd, false);
        }
        public DataTable GetSaldoAkhirDetail(int iCompanyId, DateTime dtmStart, DateTime dtmEnd, bool bForceRefresh)
        {
            String sxCacheKey = String.Format("{0}{1}_{2}_{3}",
                KCache.V_REPORT_SALDO_AKHIR_DETAIL,
                iCompanyId.ToString(),
                dtmStart.ToShortDateString(),
                dtmEnd.ToShortDateString());

            if(bForceRefresh)
            {
                HttpRuntime.Cache.Remove(sxCacheKey);
            }

            if (HttpRuntime.Cache[sxCacheKey] == null)
            {
                DBLib dbl = new DBLib(Connection.DWLA_LA_DWH);

                List<OracleParameter> lsParams = new List<OracleParameter>();

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmID";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = iCompanyId;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmStart";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmStart;

                //lsParams.Add(new OracleParameter());
                //lsParams[lsParams.Count - 1].ParameterName = "prmEnd";
                //lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Date;
                //lsParams[lsParams.Count - 1].Value = dtmEnd;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmStartYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmStart.Year;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndMonth";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Month;

                lsParams.Add(new OracleParameter());
                lsParams[lsParams.Count - 1].ParameterName = "prmEndYear";
                lsParams[lsParams.Count - 1].OracleDbType = OracleDbType.Number;
                lsParams[lsParams.Count - 1].Value = dtmEnd.Year;

                String sxQuery =
                    @"
                    SELECT     dpel.company_no,
                               dpel.company_name,
                               dper.MONTH_NUM,
                               dper.month_name,
                               dper.year_num,
                               SUM (a.amount_aft_disc_idr_total) Jumlah_Pendapatan,
                               COUNT (DISTINCT a.CONTRACT_NO) Jumlah_Jaringan
                        FROM   dim_period dper,
                               t_fact_saldo_akhir_target a,
                               dim_pelanggan dpel,
                               dim_organisasi_sales dos,
                               dim_network_owner dno
                       WHERE       a.network_owner_key = dno.network_owner_key
                               AND a.pelanggan_key = dpel.pelanggan_key
                               AND a.period_bulan_data_key = dper.period_key
                               AND a.organisasi_sales_key = dos.organisasi_sales_key
                               --AND dper.year_num = 2011
                               --AND dper.MONTH_NUM = 5
                               AND to_date('1\' || :prmStartMonth || '\' || :prmStartYear, 'dd\mm\yyyy') <= to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND ADD_MONTHS(to_date('1\' || :prmEndMonth || '\' || :prmEndYear, 'dd\mm\yyyy'), 1) > to_date(dper.DAY_NUM || '\' || dper.MONTH_NUM || '\' || dper.year_num, 'dd\mm\yyyy')
                               AND dpel.COMPANY_ID = :prmID --103893
                    GROUP BY   dper.year_num,
                               dper.month_name,
                               dper.MONTH_NUM,
                               dpel.company_no,
                               dpel.company_name
                               --dper.DAY_NUM
                    order by   dper.year_num,
                               dper.MONTH_NUM 
                    ";

                DataTable dt = dbl.GetDataTable(sxQuery, lsParams.ToArray());

                AddToCache(sxCacheKey, dt, CacheType.Daily);
                
            }
            return (DataTable)HttpRuntime.Cache[sxCacheKey];
        }        

        public override String GetCacheFilename()
        {
            return KCache.Filename.COMPANY_REPORT_VIEWS;
        }
    }
}